/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package facades;

import entities.Candidate;
import entities.Company;
import entities.CompanyAdquiredProfile;
import entities.CompanyFolder;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author damfercer
 */
@Stateless
public class CompanyAdquiredProfileFacade extends AbstractFacade<CompanyAdquiredProfile> {
    @PersistenceContext(unitName = "ProyectoPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public CompanyAdquiredProfileFacade() {
        super(CompanyAdquiredProfile.class);
    }
    
    public List<CompanyAdquiredProfile> getFilteredProfiles(String sql,Company c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT company_adquired_profile.* FROM company_adquired_profile "
                + "LEFT JOIN company_adquired_profile_folder ON company_adquired_profile.ID_COMPANY_ADQUIRED_PROFILE=company_adquired_profile_folder.COMPANY_ADQUIRED_PROFILE "
                + "LEFT JOIN candidate ON company_adquired_profile.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND company_adquired_profile.ID_COMPANY_ADQUIRED_PROFILE IN(SELECT ID_COMPANY_ADQUIRED_PROFILE FROM company_adquired_profile LEFT JOIN company_adquired_profile_folder ON company_adquired_profile.ID_COMPANY_ADQUIRED_PROFILE=company_adquired_profile_folder.COMPANY_ADQUIRED_PROFILE  WHERE company_adquired_profile_folder.COMPANY_FOLDER=");
        sb.append(c.getIdCompany());
        sb.append(")");
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), CompanyAdquiredProfile.class);
        return searchCandidatesQuery.getResultList();
    }
    
    public List<CompanyAdquiredProfile> getFilteredProfiles(String sql,CompanyFolder c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT company_adquired_profile.* FROM company_adquired_profile "
                + "LEFT JOIN company_adquired_profile_folder ON company_adquired_profile.ID_COMPANY_ADQUIRED_PROFILE=company_adquired_profile_folder.COMPANY_ADQUIRED_PROFILE "
                + "LEFT JOIN candidate ON company_adquired_profile.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND company_adquired_profile.ID_COMPANY_ADQUIRED_PROFILE IN(SELECT ID_COMPANY_ADQUIRED_PROFILE FROM company_adquired_profile LEFT JOIN company_adquired_profile_folder ON company_adquired_profile.ID_COMPANY_ADQUIRED_PROFILE=company_adquired_profile_folder.COMPANY_ADQUIRED_PROFILE  WHERE company_adquired_profile_folder.COMPANY_FOLDER=");
        sb.append(c.getIdCompanyFolder());
        sb.append(")");
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), CompanyAdquiredProfile.class);
        return searchCandidatesQuery.getResultList();
    }
    
    public List<CompanyAdquiredProfile> getFilteredProfiles(String sql){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT company_adquired_profile.* FROM company_adquired_profile "
                + "LEFT JOIN company_adquired_profile_folder ON company_adquired_profile.ID_COMPANY_ADQUIRED_PROFILE=company_adquired_profile_folder.COMPANY_ADQUIRED_PROFILE "
                + "LEFT JOIN candidate ON company_adquired_profile.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), CompanyAdquiredProfile.class);
        return searchCandidatesQuery.getResultList();
    }
    
    public CompanyAdquiredProfile find(Candidate candidate, Company company){
        CompanyAdquiredProfile res=null;
        Query namedQuery=em.createNamedQuery("CompanyAdquiredProfile.findByCandidateAndCompany");
        namedQuery.setParameter("idCandidate", candidate.getIdCandidate());
        namedQuery.setParameter("idCompany", company.getIdCompany());
        List<CompanyAdquiredProfile> companyAdquiredProfiles=namedQuery.getResultList();
        if(!companyAdquiredProfiles.isEmpty()){
            res=companyAdquiredProfiles.get(0);
        }
        return res;
    }
}
